package han.study.preparedstatement.curd;

import han.study.utils.JDBCUtil2;
import han.study.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * 使用preparedStatement来替换Statement，实现对数据表的增删改查
 * 增删改的操作
 *
 *
 *
 */
public class PrepareStatementUpdateTest {
    @Test
    public void testInsert () throws Exception {
        // 准备数据库连接
        Connection conn = JDBCUtils.getConnection();
        System.out.println(conn);
        // 准备sql
        String sql = "insert into departments(department_name,manager_id,location_id)values(?,?,?)";
        // 准备 PreparedStatement
        PreparedStatement ps = conn.prepareStatement(sql);
        // 填充占位符
        ps.setString(1,"One");
        ps.setNull(2, Types.INTEGER);
        ps.setNull(3,Types.INTEGER);
        // 用户执行操作
        ps.execute();
        JDBCUtils.closeResource(conn,ps);
        System.out.println("执行结束");
    }

    @Test
    public void testUpdate(){
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtils.getConnection();
            // 准备SQL语句
            String sql = "update user set password=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"123456");
            ps.setInt(2,5);
            ps.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtils.closeResource(conn,ps);
        }
        System.out.println("更新视图结束");
    }
    // 通用增删改的操作
    public void update(String sql,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtils.getConnection();
            ps= conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            ps.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtils.closeResource(conn,ps);
        }
    }

    // 使用common-dbutil 来实现增删改查
    @Test
    public void testInsert2(){
        Connection conn = null;
        try{
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtil2.getConnection();
            String sql = "insert into customers(name,email,birth)values(?,?,?)";
            int insertCount = runner.update(conn,sql,"蔡徐坤","caixukun@163.com","2000-01-01");
            System.out.println("添加了"+insertCount+"条记录");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil2.closeResource(conn,null);
        }
    }
}
